﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace Bills.Database
{
    public class dtableProvider
    {
        private string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\billsapp\\bills.mdb";
        private string query = "SELECT * FROM bills";

        //create a DataTable to hold the query results
        DataTable dTable = new DataTable();

        public dtableProvider()
        {
        
        }

        public void ConnectDB(DataGridView grid)
        {

            //Create an OleDbDataAdapter to execute the query
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

            //create a command builder
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);


            //fill the DataTable
            dAdapter.Fill(dTable);

            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();

            //set the BindingSource DataSource
            bSource.DataSource = dTable;

            //set the DataGridView DataSource
            grid.DataSource = bSource;
            grid.ReadOnly = true;
        }

        public void Update(string company, double amount, string date, int id=0)
        {
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);   
            dAdapter.Update(dTable);
            if (!id.Equals("0"))
            {
                query = "INSERT INTO bills VALUES (" + id + "," + company + "," + amount + "," + date + ")";
                dTable.Rows.Add(id, company, amount, date);
            }                                           
        }

    }
}
